package com.example.taskmanager;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;

public class DatabaseHelper {

    private static final String URL = "jdbc:sqlite:db/tasks.db";

    public static Connection connect() {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(URL);
            System.out.println("已建立与SQLite的连接。");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

    public static void createNewTable() {
        String sql = "CREATE TABLE IF NOT EXISTS tasks (\n"
                + "    id integer PRIMARY KEY,\n"
                + "    name text NOT NULL,\n"
                + "    description text,\n"
                + "    status text,\n"
                + "    due_date text,\n"
                + "    group_name text\n"
                + ");";

        try (Connection conn = connect();
             Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
            System.out.println("表已创建。");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void alterTable() {
        try (Connection conn = connect();
             Statement stmt = conn.createStatement()) {

            DatabaseMetaData dbMeta = conn.getMetaData();
            ResultSet rs = dbMeta.getColumns(null, null, "tasks", "group_name");
            if (!rs.next()) {
                String sql = "ALTER TABLE tasks ADD COLUMN group_name text;";
                stmt.execute(sql);
                System.out.println("表已更新，添加group_name字段。");
            } else {
                System.out.println("表已包含group_name字段。");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void main(String[] args) {
        createNewTable();
        alterTable();
    }
}
